package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cggl.Cgd;
import com.hnkywl.sts.entity.sys.Zdj;
import com.hnkywl.sts.entity.xsgl.Kh;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-4-23
 * Time: 下午2:21
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class KhDao extends SimpleHibernateTemplate<Kh, Long> {
    public Page<Kh> findAllKhList(String khbm, String jtwzname, String jtwzggxh, String jtwzbm, Long ckid, Long xsgsId, Long sbgsId, Long cdid, Long wzggid, int pageNo, int pageSize) {
        Page<Kh> page = new Page<Kh>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("asc");
        page.setOrderBy("jtwz.oldcode");
        Criteria countCriteria = getSession().createCriteria(this.entityClass)
                .setProjection(Projections.rowCount());

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("jtwz", "jtwz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("ck", "ck", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("khbm as khbm", new String[]{"khbm"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("kcbj").as("kcbj"))
                        .add(Property.forName("jtwz.id").as("jtwz_id"))
                        .add(Property.forName("jtwz.oldcode").as("jtwz_oldcode"))
                        .add(Property.forName("jtwz.code").as("jtwz_code"))
                        .add(Property.forName("jtwz.name").as("jtwz_name"))
                        .add(Property.forName("jtwz.th").as("jtwz_th"))
                        .add(Projections.sqlProjection("jtwz1_.cz||' '||jtwz1_.ggxh as jtwz_ggxh", new String[]{"jtwz_ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("chd").as("chd"))
                        .add(Property.forName("jz").as("jz"))
                        .add(Property.forName("jtwz.dw").as("jtwz_dw"))
                        .add(Property.forName("cd.id").as("cd_id"))
                        .add(Property.forName("cd.z").as("cd_z"))
                        .add(Property.forName("ck.id").as("ck_id"))
                        .add(Property.forName("ck.mc").as("ck_mc"))
                        .add(Projections.sqlProjection("js as js", new String[]{"js"}, new Type[]{Hibernate.INTEGER}))
                        .add(Projections.sqlProjection("sl as sl", new String[]{"sl"}, new Type[]{Hibernate.DOUBLE}))
                        .add(Projections.sqlProjection("kkdsl as kkdsl", new String[]{"kkdsl"}, new Type[]{Hibernate.DOUBLE}))
                        .add(Property.forName("wsdj").as("wsdj"))
                        .add(Projections.sqlProjection("rkrq as rkrq", new String[]{"rkrq"}, new Type[]{Hibernate.DATE})))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(khbm)) {
            criteria.add(Property.forName("khbm").like(khbm, MatchMode.START));
            countCriteria.add(Property.forName("khbm").like(khbm, MatchMode.START));
        }
        if (StringUtils.isNotEmpty(jtwzname)) {
            criteria.add(Property.forName("jtwz.name").like(jtwzname, MatchMode.START));
            countCriteria.add(Property.forName("jtwz.name").like(jtwzname, MatchMode.START));
        }
        if (StringUtils.isNotEmpty(jtwzggxh)) {
            criteria.add(Restrictions.or(
                    Property.forName("jtwz.ggxh").like(jtwzggxh, MatchMode.ANYWHERE),
                    Property.forName("jtwz.cz").like(jtwzggxh, MatchMode.ANYWHERE)));
            countCriteria.add(Restrictions.or(
                    Property.forName("jtwz.ggxh").like(jtwzggxh, MatchMode.ANYWHERE),
                    Property.forName("jtwz.cz").like(jtwzggxh, MatchMode.ANYWHERE)));
        }
        if (StringUtils.isNotEmpty(jtwzbm)) {
            criteria.add(Restrictions.or(
                    Property.forName("jtwz.code").like(jtwzbm, MatchMode.START),
                    Property.forName("jtwz.oldcode").like(jtwzbm, MatchMode.START)));
        }
        if (ckid != null)
            criteria.add(Restrictions.eq("ck.id", ckid));

        if (xsgsId != null && sbgsId != null) {
            Restrictions.eq("cggs.id", xsgsId);
            Restrictions.eq("sbgs.id", sbgsId);
//            criteria.add(Restrictions.or(, ));
        } else {
            if (xsgsId != null)
                criteria.add(Restrictions.eq("cggs.id", xsgsId));
            if (sbgsId != null)
                criteria.add(Restrictions.eq("sbgs.id", sbgsId));
        }

        if (wzggid != null)
            criteria.add(Restrictions.eq("jtwz.id", wzggid));

        criteria.add(Restrictions.ne("kkdsl", 0d));
        criteria.add(Restrictions.ne("sl", 0d));
        criteria.add(Restrictions.eq("qk", false));
        criteria.addOrder(Order.desc("jtwz.cz"));
        criteria.addOrder(Order.desc("jtwz.ggxh"));
        criteria.addOrder(Order.asc("rkrq"));
        return this.findByCriteria(page, criteria);
    }


    public void updateKhKdsl(Long id, Double sl) {
        java.text.DecimalFormat df = new java.text.DecimalFormat("#0.0000");
        String slstr = df.format(sl);
        sl = Double.parseDouble(slstr);

        String hql = "update Kh set kkdsl=kkdsl-? where id=?";
        Query query = this.getSession().createQuery(hql);
        query.setDouble(0, sl);
        query.setLong(1, id);
        query.executeUpdate();
    }

    public void updateKhKdslAdd(Long id, Double sl) {
        java.text.DecimalFormat df = new java.text.DecimalFormat("#0.0000");
        String slstr = df.format(sl);
        sl = Double.parseDouble(slstr);

        String hql = "update Kh set kkdsl=kkdsl+? where id=?";
        Query query = this.getSession().createQuery(hql);
        query.setDouble(0, sl);
        query.setLong(1, id);
        query.executeUpdate();
    }

    public void updateKhSl(Long id, Double sl) {
        java.text.DecimalFormat df = new java.text.DecimalFormat("#0.0000");
        String slstr = df.format(sl);
        sl = Double.parseDouble(slstr);

        String hql = "update Kh set kkdsl=kkdsl-?,sl=sl-? where id=?";
        Query query = this.getSession().createQuery(hql);
        query.setDouble(0, sl);
        query.setDouble(1, sl);
        query.setLong(2, id);
        query.executeUpdate();
    }

    public void updateKhSlAdd(Long id, Double sl) {
        java.text.DecimalFormat df = new java.text.DecimalFormat("#0.0000");
        String slstr = df.format(sl);
        sl = Double.parseDouble(slstr);

        String hql = "update Kh set kkdsl=kkdsl+?,sl=sl+? where id=?";
        Query query = this.getSession().createQuery(hql);
        query.setDouble(0, sl);
        query.setDouble(1, sl);
        query.setLong(2, id);
        query.executeUpdate();
    }

    /**
     * 根据仓库ID查询卡号
     *
     * @param ckId
     * @return
     */
    public List<Kh> findKhByCkId(Long ckId) {
        return find("from Kh where ck.id = ?", ckId);
    }

    /**
     * @param cgd
     * @return
     */
    public String findKhByCgd(Cgd cgd) {
        String sql = " select max(pxh) from t_biz_kh where cgd_id = ?";
        return (createSQLQuery(sql).setParameter(0, cgd.getId()).uniqueResult() == null ? 0 : (BigDecimal) createSQLQuery(sql).setParameter(0, cgd.getId()).uniqueResult()).toString();
    }

    /**
     * 查找未出库卡号
     *
     * @param
     * @return
     */
    public List<Kh> findWckKh() {
        return find("from Kh where    sl > 0 and qk = 0 ");
    }

    public BigDecimal findPxhByCgd(Long id) {
        String sql = "select max(pxh) from t_biz_kh where cgd_id = ? ";
        return (BigDecimal) createSQLQuery(sql).setParameter(0, id).uniqueResult();
    }


    public Kh findKhByRsn(String resource_serial_number_1, String bindNo) {
        String hql = "from Kh where resource_serial_number_1  =? or bindNo =?";
        List<Kh> hlist = this.find(hql, resource_serial_number_1, bindNo);
        if (null != hlist && hlist.size() > 0) {
            return hlist.get(0);
        }
        return null;
    }
}
